package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mbc.common.dao.DAO;
import com.mbc.rmt.data.TypeDataList;
import com.mbc.rmt.entities.Type;
import com.mbc.rmt.entities.TypeRpt;

public class TypeDAOImpls extends DAO<Type> implements TypeDAO{

	public TypeDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "Type";
	}
	
	@Override
	public void create(Type entity) throws SQLException {
		query = "insert into " + table_name + " ( type_id, type_code, description, branchId) values( ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
				pstmt.setLong(i++, entity.getType_id());
		pstmt.setString(i++, entity.getType_code());
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void update(Type entity) throws SQLException {
		query = "update " + table_name + " set Description = ?, BranchId = ?" + 
				" where type_id = " + entity.getType_id();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getDescription());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.executeUpdate();
		pstmt.close();
		
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where type_id = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
		
	}
	@Override
	public Type findByCode(String code, long branchId) throws SQLException {
		Type l_Type = null;
		if(code != "")
			query = getSelectQuery() + " where type_code = '" + code + "' and branchId = " + branchId;
		else
			query =  getSelectQuery() + " where  branchId = " + branchId;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_Type = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_Type;
	}
	public TypeDataList getTypeList(TypeRpt type) throws SQLException {
		/*ArrayList<Type> l_TypeList = new ArrayList<Type>();
		String l_query = "dbo.TypeRpt ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		TypeRptParameter(l_pstmt, from,to);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_TypeList.add(readRecordFT(l_rs));
		}*/
		TypeDataList l_DataList = new TypeDataList();
		ArrayList<Type> l_TypeList = new ArrayList<Type>();
		String l_query = "dbo.sp_cr_TypeRpt ?, ?, ?";
		CallableStatement l_pstmt = conn.prepareCall(l_query);
		TypeRptParameter(l_pstmt, type);
		ResultSet l_rs = l_pstmt.executeQuery();
		while (l_rs.next()) {
			l_TypeList.add(readRecordFT(l_rs));
		}
		l_DataList.setEntityList(l_TypeList);
		l_rs.close();
		l_pstmt.close();
		return l_DataList;
	}

	@Override
	public TypeDataList findBanks(Type cri) throws SQLException {
		TypeDataList l_DataList = new TypeDataList();
		ArrayList<Type> l_entityList = new ArrayList<Type>();
		/*String l_cri = " and (type_code like N'%" + cri.getKeyword() + "%'" +
				" or Description like N'%" + cri.getKeyword() + "%')"
				+ " and BranchId = " + cri.getBranchId();*/
		
		String l_cri = " and (" + cri.getFieldname() + " like N'%" + cri.getKeyword() + "%') and BranchId = "+ cri.getBranchId();
		
		query = "set rowcount " + cri.getSize() +
			" select * from (select row_number() over(order by type_id desc) as [rowno], * from " + table_name +
			" where 1 = 1" + l_cri + ")" + table_name +
			" where rowno > " + cri.getIndex();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {	
			l_entityList.add(readRecord(rs));
		} 
		l_DataList.setEntityList(l_entityList);
		rs.close();
		pstmt.close();
		query = "select count(type_id) from " + table_name + " where 1 = 1" + l_cri;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_DataList.setTotalRecord(rs.getLong(1));
		}
		rs.close();
		pstmt.close();
		return l_DataList;
	}

	@Override
	protected Type readRecord(ResultSet rs) throws SQLException {
		Type l_entity = new Type();
		l_entity.setType_id(rs.getLong("type_id"));
		l_entity.setType_code(rs.getString("type_code"));
		l_entity.setDescription(rs.getString("description"));
		
		return l_entity;
	}
	
	private Type readRecordFT(ResultSet rs) throws SQLException {
		Type l_entity = new Type();
		l_entity.setType_code(rs.getString("type_code"));
		l_entity.setDescription(rs.getString("description"));
		return l_entity;
	}
	
	public ResultSet prepareTypeReport(TypeRpt type)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_TypeRpt ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		TypeRptParameter(l_pstmt, type);
		l_rs = l_pstmt.executeQuery();
		return l_rs;

	}
	private static void TypeRptParameter(CallableStatement l_pstmt,
			TypeRpt type) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, type.getFromTypeID());
		l_pstmt.setString(i++, type.getToTypeID());
		l_pstmt.setLong(i++, type.getBranchID());
	}	

}
